import json
from datetime import datetime

import requests
from urllib.parse import urljoin

import pymysql

# 船舶识别号授予数量
BASE_URL = 'http://cmp.msa.gov.cn/cbdj_mgt/shipidApproval/shipIdQuery.action?act=queryAccount&shipIdQO.reg_org_code=140000&shipIdQO.apply_date_start=2024-10-01&shipIdQO.apply_date_end=2024-12-31&page=1&rows=10'
headers = {
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/json;charset=utf-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': 'B%2FSwdX%2FBJjHv859QBWZBfb%2FC61rj9SaLLjB5U6x5apKGn%2BabUTQDgrxaUhl3XrChOg0oTNL65C%2F%2BJkL6ou139%2F2xOwZEV30W63d1R37ipVSpCIDYw7Drt5XjaOoxoLOk6ZyQkCIYem1QCZLmmsvOU78L81xa2ecliRC18CvGz7MX9PRo6k2uuZy6fPFQd8bC9l29t%2FXlkyVHd51h%2FR0x25VYr2mF9VB4BSvYI7r35px20U2AmPoKMZPGDW%2FUEZraOtcD1%2BL%2Bz2l8llsZkbP2BFqocZjnXzzgyaTaHi9RPto46Fa3pfQg9UhAai1TGCIbcgsZwJE3uTpaKAfCgUBl9Wmn%2BYES3UzHn8L74fx7oCGmIVCFq%2FeLZCLPQg6%2FBt1g%2BtVQy%2BWt%2FXucTDL6TEYjBf7ZN%2FIW9M1L1%2FxJV0ANqZog3a3A1BASeQEmW1PVfM7%2FzZqeOOXBjPNLat2DV5x%2FijSomTGqmN58MU26s1NLpkY%3D',

    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': 'YANld0tMLZWh7nIhyLArc6tQVPlGkARQY2MBk-Ntz9kJxYYvJtYC!-1345020859'
}

session = requests.Session()
response_lo = session.post(BASE_URL, headers=headers, cookies=cookies)
print(response_lo.text)
strData = json.loads(response_lo.text)

if response_lo.status_code == 200:
    # 获取接口数据
    # html_content = response_lo.text
    # data = json.loads(response_lo.text)
    # print(">>>"+html_content)
    # 接口数据转换成json
    # data = json.loads(html_content)
    # 截取需要保存的数据
    # dataValue = data['datas']['table']['row']['table']['row']
    # print(data[1])

    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'xt_register_identify_number_grant_ship'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # print("!!!"+fields)
                # print("~~~"+values)
                # names = record['agent_name']
                # print(names)
                print(tuple(record.keys()))
                # 构建插入语句
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                # print("%%%" + insert_stmt)
                # print(">>>" + str(record.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()